<?php
class M_Tour extends Apps_Db_Table_Abstract {
    protected $_name = 'tour';

    //list tour by category
    public function listTourByCategoryId($categoryId, $countryId = 1, $limit = 6) {
        if(!$categoryId) {
            return null;
        }
        $q = $this->_db
                  ->select()
                  ->from('tour', null)
                  ->join('tour_category', 'tour_category.id = tour.tour_category_id', null)
                  ->join('article', 'article.reference_id = tour.id', null)
                  ->where('article.reference_type =? ', M_DbTable_Article::REFERENCE_TYPE_TOUR)
                  ->where('tour_category.id =? ', $this->_db->quote($categoryId))
                  ->where('tour.status =? ', 1)
                  ->where('tour.country_id =? ', $this->_db->quote($countryId))
                  ->order('tour.sorting_number ASC')
                  ->limit($limit)
                  ->columns(array('id'                  => 'tour.id',
                                  'thumbnail'           => 'article.thumbnail'));

        if($this->getLang() == 'en') {
            $q->columns(array('title'            => 'article.en_title',
                              'overview'         => 'article.en_overview'));
        } else $q->columns(array('title'            => 'article.title',
                                 'overview'         => 'article.overview'));
        return $q;
    }

    //list tour by tour style(trip in style)
    public function listTourByStyleId($styleId, $countryId = 1) {
        if(!$styleId) {
            return null;
        }
        $q = $this->_db
                  ->select()
                  ->from('tour', null)
                  ->join('tour_style', 'tour_style.id = tour.tour_style_id', null)
                  ->join('article', 'article.reference_id = tour.id', null)
                  ->where('article.reference_type =? ', M_DbTable_Article::REFERENCE_TYPE_TOUR)
                  ->where('tour_style.id =? ', $this->_db->quote($styleId))
                  ->where('tour.status =? ', 1)
                  ->where('tour.country_id =? ', $this->_db->quote($countryId))
                  ->order('tour.sorting_number ASC')
                  ->columns(array('id'                  => 'tour.id',
                                  'thumbnail'           => 'article.thumbnail'));

        if($this->getLang() == 'en') {
            $q->columns(array('title'            => 'article.en_title',
                              'overview'         => 'article.en_overview'));
        } else $q->columns(array('title'            => 'article.title',
                                 'overview'         => 'article.overview'));
        return $q;
    }

    //get tour by id
    public function getTourById($id) {
        $q = $this->_db
                  ->select()
                  ->from('tour', null)
                  ->join('article', 'article.reference_id = tour.id', null)
                  ->where('article.reference_type =? ', M_DbTable_Article::REFERENCE_TYPE_TOUR)
                  ->where('tour.status =? ', 1)
                  ->where('tour.id =? ', $this->_db->quote($id))
                  ->columns(array('id'                  => 'tour.id',
                                  'meta_keyword'        => 'tour.meta_keyword',
                                  'meta_description'    => 'tour.meta_description',
                  				  'header_title'		=> 'tour.header_title',
                                  'thumbnail'           => 'article.thumbnail'));

        if($this->getLang() == 'en') {
            $q->columns(array('title'            => 'article.en_title',
                              'note_price'       => 'tour.en_note_price',
                              'detail_thumbnail' => 'tour.detail_thumbnail',
                              'overview'         => 'article.en_overview',
                              'detail'           => 'article.en_detail',
                              'itinerary'        => 'tour.en_itinerary'));
        } else {
            $q->columns(array('title'            => 'article.title',
                              'note_price'       => 'tour.note_price',
                              'detail_thumbnail' => 'tour.detail_thumbnail',
                              'overview'         => 'article.overview',
                              'detail'           => 'article.detail',
                              'itinerary'        => 'tour.itinerary'));
        }
        return $q;
    }

    //list promotion tour
    public function listPromotionTour($limit = false) {
        $q = $this->_db
                  ->select()
                  ->from('tour', null)
                  ->join('article', 'article.reference_id = tour.id', null)
                  ->where('article.reference_type =? ', M_DbTable_Article::REFERENCE_TYPE_TOUR)
                  ->where('tour.status =? ', 1)
                  ->where('tour.special_offer =? ', 1)
                  ->order('tour.sorting_number ASC')
                  ->columns(array('id'                  => 'tour.id',
                                  'thumbnail'           => 'article.thumbnail'));
        if($limit) {
            $q->limit($limit);
        }

        if($this->getLang() == 'en') {
            $q->columns(array('title'            => 'article.en_title',
                              'overview'         => 'article.en_overview'));
        } else {
            $q->columns(array('title'            => 'article.title',
                              'overview'         => 'article.overview'));
        }
        return $q;
    }

    // List Tour Active
    public function getListActive() {
		$q = $this->_db
				  ->select()
				  ->from(array('t'   => $this->_name), null)
				  ->join(array('a' 	 => 'article'), 'a.reference_id = t.id', null)
				  ->where('a.reference_type =?', M_DbTable_Article::REFERENCE_TYPE_TOUR)
				  ->where('t.status = ? ', M_DbTable_Tour::STATUS_ACTIVE)
				  ->order('t.created_date DESC')
				  ->columns(
				  		array('id'					=> 't.id',
							  'title'				=> 'a.title',
				  ));
        return $q;
	}

    //list tour by country
    public function listTourByCountryId($countryId, $limit = 4) {
        if(!$countryId) {
            return null;
        }
        $q = $this->_db
                  ->select()
                  ->from('tour', null)
                  ->join('article', 'article.reference_id = tour.id', null)
                  ->where('article.reference_type =? ', M_DbTable_Article::REFERENCE_TYPE_TOUR)
                  ->where('tour.country_id =? ', $this->_db->quote($countryId))
                  ->where('tour.status =? ', 1)
                  ->order('tour.sorting_number ASC')
                  ->limit($limit)
                  ->columns(array('id'                  => 'tour.id',
                                  'thumbnail'           => 'article.thumbnail'));

        if($this->getLang() == 'en') {
            $q->columns(array('title'            => 'article.en_title',
                              'overview'         => 'article.en_overview'));
        } else $q->columns(array('title'            => 'article.title',
                                 'overview'         => 'article.overview'));
        return $q;
    }

}